package com.lh;

import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.parser.SQLStatementParser;

import java.util.HashSet;
import java.util.List;
import java.util.Set;

/**
 * @author: linghao
 * @since: 2023/4/6
 */
public class DruidSqlParser {

    public static Set<String> needItem =  new HashSet<>();

    public static void main(String[] args) throws Exception {
        String sql = "select class, test1,test2," +
                "sum(case when mean>80 then 1 else 0 end) as numover80, " +
                "sum(case when mean>80 then 1 else 0 end)/count(id) as percentage\n" +
                "from tb_student s left join (\n" +
                "select id,xxx1,xxx2, avg(score) as mean from tb_score group by id) as t on s.id = t.id\n" +
                "group by class\n";
//        parserSql(sql);
        generateNewSql(sql);
    }

    /*
        sql最外层select 字段遍历每次保留一个字段形成一个新sql
        子查询的需要保留的字段通过每次参数带入递归中
     */
    public static void generateNewSql(String sql) throws Exception{
        SQLSelectQueryBlock sqlSelectQueryBlock = getSqlSelectQueryBlock(sql);
        List<SQLSelectItem> selectList = sqlSelectQueryBlock.getSelectList();
        for(int i=0;i<selectList.size();i++){
            int finalI = i;
            sqlSelectQueryBlock = getSqlSelectQueryBlock(sql);
            List<SQLSelectItem> selectList2 = sqlSelectQueryBlock.getSelectList();
            selectList2.removeIf(item -> item != selectList2.get(finalI));
            needItem.add("mean");
            parserSubSql(sqlSelectQueryBlock,false);
//            parserSubSql(sqlSelectQueryBlock.toString(),false);
            System.out.println(sqlSelectQueryBlock.toString()+";");
        }
    }

    public static void parserSubSql(SQLSelectQueryBlock sqlSelectQueryBlock,boolean boo) throws Exception{
        List<SQLSelectItem> selectList = sqlSelectQueryBlock.getSelectList();
//        selectList.removeIf(item -> 删除除了包含 itemsval 的其他所有字段);
        if(boo){
            selectList.removeIf(item -> item != selectList.get(1));
        }
        SQLTableSource from = sqlSelectQueryBlock.getFrom();
        SQLExpr where = sqlSelectQueryBlock.getWhere();
        parserFrom(from);
//        parserSubSql(where,"获取selectList 删除后的 所有字段");
    }

   /* public static void parserSubSql(String sql,boolean boo) throws Exception{
        SQLSelectQueryBlock sqlSelectQueryBlock = getSqlSelectQueryBlock(sql);
        List<SQLSelectItem> selectList = sqlSelectQueryBlock.getSelectList();
//        selectList.removeIf(item -> 删除除了包含 itemsval 的其他所有字段);
        if(boo){
            selectList.removeIf(item -> item != selectList.get(1));
        }
        SQLTableSource from = sqlSelectQueryBlock.getFrom();
        SQLExpr where = sqlSelectQueryBlock.getWhere();
        parserFrom(from);
//        parserSubSql(where,"获取selectList 删除后的 所有字段");
    }*/

    private static void parserFrom(SQLTableSource from) throws Exception {
        if(from instanceof SQLSubqueryTableSource){
            parserSubSql(((SQLSubqueryTableSource) from).getSelect().getQueryBlock(),true);
        }else if(from instanceof SQLJoinTableSource){
            parserFrom(((SQLJoinTableSource) from).getLeft());
            parserFrom(((SQLJoinTableSource) from).getRight());
        }else if(from instanceof SQLExprTableSource){
//            System.out.println(from);
        }
    }

    public static SQLSelectQueryBlock getSqlSelectQueryBlock(String sql){
        SQLStatementParser statementParser = new SQLStatementParser(sql);
        SQLSelectStatement statement = (SQLSelectStatement)statementParser.parseStatement();
        SQLSelect select = statement.getSelect();
        return select.getQueryBlock();
    }
}
